﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;

using Bpms.Data;
using Bpms.Entity;
using Bpms.Log;
using Bpms.Common;

namespace Bpms.Data.SqlServer
{
    public partial class DataProvider : IDataProvider
    {
        #region Private

        // 当前文件对应的逻辑类名，用于写日志
        string jobTitleClassName = "Bpms.Data.SqlServer.JobTitle";

        /// <summary>
        /// 绑定数据到职位实体
        /// </summary>
        /// <param name="idr">idatareader</param>
        /// <returns>IList<JobTitleInfo>对象</returns>
        IList<JobTitleInfo> GetDataBindedJobTitle(IDataReader idr)
        {
            IList<JobTitleInfo> jobTitileList = new List<JobTitleInfo>();

            while (idr.Read())
            {
                JobTitleInfo jobTitle = new JobTitleInfo();

                jobTitle.JobTitleID = (int)idr["job_title_id"];                 // 标识ID（职位ID）
                jobTitle.OrganizationID = (int)idr["organization_id"];          // 所属部门ID
                jobTitle.Name = idr["name"].ToString();                         // 职位名称（部门名称、职位名称）
                jobTitle.Remark = idr["remark"].ToString();                     // 备注
                jobTitle.IsDelete = Convert.ToBoolean(idr["is_delete"]);                     // 是否删除（0：否，1：是）

                jobTitileList.Add(jobTitle);
            }

            return jobTitileList;
        }

        /// <summary>
        /// 根据sql语句返回职位信息
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns>职位对象</returns>
        JobTitleInfo GetJobTitleBySQL(string sql)
        {
            IList<JobTitleInfo> jobTitleList = GetJobTitleListBySQL(sql);

            if (jobTitleList.Count > 0)
            {
                return jobTitleList[0];
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 根据sql语句返回职位列表
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns> IList<JobTitleInfo>对象</returns>
        IList<JobTitleInfo> GetJobTitleListBySQL(string sql)
        {
            IList<JobTitleInfo> jobTitleList = new List<JobTitleInfo>();

            using (IDataReader idr = DbHelper.ExecuteReader(CommandType.Text, sql) as IDataReader)
            {
                try
                {
                    jobTitleList = GetDataBindedJobTitle(idr);
                }
                catch (Exception e)
                {
                    log.WriteLog(this.jobTitleClassName + "：获取绑定的职位信息时出现异常", e);
                }
                finally
                {
                    if (idr != null)
                    {
                        idr.Close();
                    }
                }
            }

            return jobTitleList;
        }

        #endregion

        /// <summary>
        /// 添加职位
        /// </summary>
        /// <param name="jobTitle">职位实体对象</param>
        /// <returns>添加是否成功（true：成功，false：失败）</returns>
        public bool InsertJobTitle(JobTitleInfo jobTitle)
        {
            bool isExecSuccessed = false;

            try
            {
                DbParameter[] parms = 
                {
                    DbHelper.MakeInParam("@job_title_id", (DbType)SqlDbType.Int, 4, jobTitle.JobTitleID),           // 标识ID（职位ID）
                    DbHelper.MakeInParam("@organization_id", (DbType)SqlDbType.Int, 4, jobTitle.OrganizationID),    // 所属部门ID
                    DbHelper.MakeInParam("@name", (DbType)SqlDbType.NVarChar, 30, jobTitle.Name),                   // 职位名称
                    DbHelper.MakeInParam("@remark", (DbType)SqlDbType.NVarChar, 200, jobTitle.Remark),              // 备注
                    DbHelper.MakeInParam("@is_delete", (DbType)SqlDbType.TinyInt, 1, jobTitle.IsDelete),            // 是否删除（0：否，1：是）
                };

                if (DbHelper.ExecuteNonQuery(CommandType.StoredProcedure, "bpms_add_job_title", parms) > 0)
                {
                    isExecSuccessed = true;
                }
            }
            catch (Exception e)
            {
                log.WriteLog(this.jobTitleClassName + "：添加职位信息时出现异常", e);
            }

            return isExecSuccessed;
        }

        /// <summary>
        /// 修改职位
        /// </summary>
        /// <param name="jobTitle">职位实体对象</param>
        /// <returns>修改是否成功（true：成功，false：失败）</returns>
        public bool UpdateJobTitle(JobTitleInfo jobTitle)
        {
            bool isExecSuccessed = false;

            try
            {
                DbParameter[] parms = 
                {
                    DbHelper.MakeInParam("@job_title_id", (DbType)SqlDbType.Int, 4, jobTitle.JobTitleID),           // 标识ID（职位ID）
                    DbHelper.MakeInParam("@organization_id", (DbType)SqlDbType.Int, 4, jobTitle.OrganizationID),    // 所属部门ID
                    DbHelper.MakeInParam("@name", (DbType)SqlDbType.NVarChar, 30, jobTitle.Name),                   // 职位名称
                    DbHelper.MakeInParam("@remark", (DbType)SqlDbType.NVarChar, 200, jobTitle.Remark)               // 备注
                };

                if (DbHelper.ExecuteNonQuery(CommandType.StoredProcedure, "bpms_update_job_title", parms) > 0)
                {
                    isExecSuccessed = true;
                }
            }
            catch (Exception e)
            {
                log.WriteLog(this.jobTitleClassName + "：修改职位信息时出现异常", e);
            }

            return isExecSuccessed;
        }

        /// <summary>
        /// 删除职位
        /// </summary>
        /// <param name="jobTitleId">职位ID</param>
        /// <returns>删除是否成功（true：成功，false：失败）</returns>
        public bool DeleteJobTitle(int jobTitleId)
        {
            bool isExecSuccessed = false;

            try
            {
                DbParameter[] parms = 
                {
                    DbHelper.MakeInParam("@job_title_id", (DbType)SqlDbType.Int, 4, jobTitleId)        // 标识ID（职位ID）
                };

                if (DbHelper.ExecuteNonQuery(CommandType.StoredProcedure, "bpms_delete_job_title", parms) > 0)
                {
                    isExecSuccessed = true;
                }
            }
            catch (Exception e)
            {
                log.WriteLog(this.jobTitleClassName + "：删除组织信息时出现异常", e);
            }

            return isExecSuccessed;
        }

        /// <summary>
        /// 判断职位名称是否存在
        /// </summary>
        /// <param name="jobTitle">职位实体对象</param>
        /// <param name="organizationId">职位所属的部门ID</param>
        /// <param name="type">判断的类型（添加或修改）</param>
        /// <returns>职位名称是否存在（true：已存在，false：不存在）</returns>
        public bool JobTitleExists(JobTitleInfo jobTitle, OperateType type)
        {
            StringBuilder sbSql = new StringBuilder("SELECT 1 FROM bp_job_title WHERE ");
            DbParameter[] parms = 
            { 
                DbHelper.MakeInParam("@job_title_id", (DbType)SqlDbType.Int, 4, jobTitle.JobTitleID), 
                DbHelper.MakeInParam("@organization_id", (DbType)SqlDbType.Int, 4, jobTitle.OrganizationID), 
                DbHelper.MakeInParam("@name", (DbType)SqlDbType.NVarChar, 30, jobTitle.Name)
            };

            switch (type)
            {
                case OperateType.Insert:
                    sbSql.AppendFormat(" [name] = @name AND organization_id = @organization_id ");
                    break;
                case OperateType.Update:
                    sbSql.AppendFormat(" [name] = @name AND organization_id = @organization_id AND [organization_id] <> @organization_id ");
                    break;
                default:
                    log.WriteLog(this.jobTitleClassName + "：检测职位名称是否存在，参数错误，未指定检测类型，将按照已经存在处理，防止添加新的同名职位名称");
                    sbSql.AppendFormat(" 1 = 2 ");
                    break;
            }
            try
            {
                if (DbHelper.ExecuteScalar(CommandType.Text, sbSql.ToString(), parms) != null)
                {
                    return true;
                }
            }
            catch (Exception e)
            {
                log.WriteLog(this.jobTitleClassName + "：判断职位名称是否存在时产生异常", e);
            }

            return false;
        }

        /// <summary>
        /// 得到职位实体对象
        /// </summary>
        /// <param name="jobTitleId">职位ID</param>
        /// <returns>职位实体对象</returns>
        public JobTitleInfo GetJobTitle(int jobTitleId)
        {
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append(" SELECT job_title_id, organization_id, [name], remark ");
            sbSql.Append(" FROM bp_job_title ");
            sbSql.Append(" WHERE is_delete = 0 AND job_title_id = ").Append(jobTitleId);

            return GetJobTitleBySQL(sbSql.ToString());
        }

        /// <summary>
        /// 得到职位列表
        /// </summary>
        /// <param name="pageId">当前页ID</param>
        /// <param name="pageSize">页大小</param>
        /// <param name="condition">提取的条件</param>
        /// <returns>职位列表</returns>
        public IList<JobTitleInfo> GetJobTitles(int pageId, int pageSize, string condition)
        {
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append(" SELECT TOP {0} job_title_id, organization_id, [name], remark, is_delete ");
            sbSql.Append(" FROM bp_job_title ");
            sbSql.Append(" WHERE {2} AND is_delete = 0 job_title_id NOT IN ");
            sbSql.Append(" ( ");
            sbSql.Append("      SELECT TOP {1} job_title_id FROM bp_job_title ");
            sbSql.Append("       WHERE {2} AND is_delete = 0 ");
            sbSql.Append("       ORDER BY job_title_id DESC ");
            sbSql.Append(" ) ");
            sbSql.Append(" ORDER BY job_title_id DESC ");

            string sql = sbSql.ToString();
            sbSql.Remove(0, sbSql.Length);
            sbSql.AppendFormat(sql, pageSize, pageSize * (pageId - 1), condition);

            return GetJobTitleListBySQL(sbSql.ToString());
        }

        /// <summary>
        /// 得到职位列表
        /// </summary>
        /// <param name="condition">提取的条件</param>
        /// <returns>职位列表</returns>
        public IList<JobTitleInfo> GetJobTitles(string condition)
        {
            StringBuilder sbSql = new StringBuilder();

            sbSql.Append(" SELECT job_title_id, organization_id, [name], remark, is_delete FROM bp_job_title ");
            sbSql.Append(" WHERE is_delete = 0 ");
            if (condition.Trim().Length != 0)
            {
                sbSql.Append(" AND ").Append(condition);
            }
            else
            {
                sbSql.Append(" AND 1=1 ");
            }

            sbSql.Append(" ORDER BY job_title_id ASC ");

            return GetJobTitleListBySQL(sbSql.ToString());
        }

        /// <summary>
        /// 根据职位ID得到职位名称
        /// </summary>
        /// <param name="jobTitleId">职位ID</param>
        /// <returns>职位名称</returns>
        public string GetJobTitleName(int jobTitleId)
        {
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append(" SELECT [name] FROM bp_job_title WHERE [job_title_id] = @job_title_id ");

            DbParameter[] parms = 
            { 
                DbHelper.MakeInParam("@job_title_id", (DbType)SqlDbType.Int, 4, jobTitleId) 
            };
            object jobTitleName = null;
            try
            {
                jobTitleName = DbHelper.ExecuteScalar(CommandType.Text, sbSql.ToString(), parms) ?? "";
            }
            catch (Exception e)
            {
                log.WriteLog(this.userClassName + "：根据职位的ID，得到职位名称时出现异常", e);
            }

            return jobTitleName.ToString();
        }
    }
}
